package com.it.xuehang.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.it.xuehang.dto.ResourceDataImportDTO;
import com.it.xuehang.entity.ResourceCategory;
import com.it.xuehang.entity.ResourceCoor;
import com.it.xuehang.entity.ResourceData;
import com.it.xuehang.mapper.ResourceCategoryMapper;
import com.it.xuehang.mapper.ResourceCoorMapper;
import com.it.xuehang.mapper.ResourceDataMapper;
import com.it.xuehang.service.ResourceCategoryService;
import com.it.xuehang.service.ResourceDataService;
import com.it.xuehang.util.GuidUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 * 资源数据 服务实现类
 * </p>
 *
 * @author xuehang
 * @since 2024-12-10
 */
@Service
@Slf4j
public class ResourceDataServiceImpl extends ServiceImpl<ResourceDataMapper, ResourceData> implements ResourceDataService {
    @Autowired
    private ResourceDataMapper resourceDataMapper;
    @Autowired
    private ResourceCategoryService resourceCategoryService;
    @Autowired
    private ResourceCoorMapper resourceCoorMapper;
    @Autowired
    private ResourceCategoryMapper resourceCategoryMapper;

    private final Validator validator;

    public ResourceDataServiceImpl() {
        ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
        this.validator = factory.getValidator();
    }



    @Override
    public void addResourceData(ResourceData resourceData) {
        resourceDataMapper.insert(resourceData);
    }

    @Override
    public void delResourceData(String resourceid) {
        resourceDataMapper.deleteById(resourceid);

    }

    @Override
    public void updateResourceData(ResourceData resourceData) {
        log.info("resourceData----->{}",resourceData);//
        resourceDataMapper.updateById(resourceData);

    }

    @Override
    public List<ResourceData> selectListByCondition(String keyword, Date startDate, Date endDate, String rescateid) {
        QueryWrapper<ResourceData> queryWrapper = new QueryWrapper<>();

        // 添加名称关键字查询
        if (StringUtils.isNotBlank(keyword)) {
            queryWrapper.like("resourceName", keyword);
        }

        // 添加入库日期范围查询
        if (startDate != null && endDate != null) {
            queryWrapper.between("intime", startDate, endDate);
        }

        // 如果rescateid存在，
        if (StringUtils.isNotBlank(rescateid)) {
            //递归调用获取资源类别列表
            List<ResourceCategory> categoriesAndSubcategories = resourceCategoryService.getCategoriesAndSubcategories(rescateid);
            //获取该类别及其所有有子类别的子类别的categoryIds列表
          List<String> categoryIds =   new ArrayList<>();
          for(ResourceCategory  category :categoriesAndSubcategories ){
              categoryIds.add(category.getRescateid());
          }
          log.info("categoryIds:{}",categoryIds);

            // 检查categoryIds是否为空，如果不为空，则添加rescateid的in查询条件
            if (!categoryIds.isEmpty()) {
                queryWrapper.in("rescateid", categoryIds);
            }
        }

        return resourceDataMapper.selectList(queryWrapper);

    }






    /**
     * 根据中心点经纬度坐标和半径查询圆形范围内的资源数据。
     *
     * @param centerLat 中心点纬度
     * @param centerLng 中心点经度
     * @param radius    半径（单位：公里）
     * @return 查询结果列表
     */
    public List<ResourceData> searchWithinCircle(Double centerLat, Double centerLng, Double radius) {
        // 获取所有资源坐标
        List<ResourceCoor> allCoors = resourceCoorMapper.selectList(null);

        List<ResourceData> result = new ArrayList<>();
        for (ResourceCoor coor : allCoors) {
            double distance =calculateDistance(centerLat, centerLng, coor.getX(), coor.getY());
            log.info("distance:{}",distance);
            if (distance <= radius) {
                result.add(this.getById(coor.getResourceid()));
            }
        }
        log.info("result:{}",result);

        return result;
    }

    /**
     * 使用Haversine公式计算两点之间的距离（单位：公里）。
     *
     * @param lat1 第一点纬度
     * @param lng1 第一点经度
     * @param lat2 第二点纬度
     * @param lng2 第二点经度
     * @return 两点之间的距离（单位：公里）
     */
    private double calculateDistance(double lat1, double lng1, double lat2, double lng2) {
        log.info("{},{},{},{}",lat1,lng1,lat2,lng2);
        final double R = 6371; // 地球半径，单位：公里
        double latDistance = Math.toRadians(lat2 - lat1);
        double lngDistance = Math.toRadians(lng2 - lng1);
        double a = Math.sin(latDistance / 2) * Math.sin(latDistance / 2)
                + Math.cos(Math.toRadians(lat1)) * Math.cos(Math.toRadians(lat2))
                * Math.sin(lngDistance / 2) * Math.sin(lngDistance / 2);
        double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        return R * c;
    }



    /**
     * 导入资源数据
     *
     * @param file 上传的Excel文件
     * @return 返回导入结果信息
     */
    public Map<String, Object> importResources(MultipartFile file) {
        List<String> errorMessages = new ArrayList<>();
        List<ResourceData> successRecords = new ArrayList<>();



//        try (Workbook workbook = new XSSFWorkbook(file.getInputStream())) {
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过标题行

                ResourceDataImportDTO dto = readRow(row);
                log.info("dto:{}",dto);

                // 验证数据
                Set<ConstraintViolation<ResourceDataImportDTO>> violations = validator.validate(dto);
                log.info("violations:{}",violations);
                if (!violations.isEmpty()) {
                    for (ConstraintViolation<ResourceDataImportDTO> violation : violations) {
                        errorMessages.add("第" + (row.getRowNum() + 1) + "行：" + violation.getMessage());
                    }
                    continue;
                }

                // 转换类别名称为ID
                String rescateId = convertChineseRescateToId(dto.getChineseRescateName());
                if (rescateId == null) {
                    errorMessages.add("第" + (row.getRowNum() + 1) + "行：资源类别不存在");
                    continue;
                }

                // 构建实体对象
                ResourceData resourceData = new ResourceData();
                resourceData.setRescateid(rescateId);
                resourceData.setResourceid(GuidUtils.generateGUID());
                resourceData.setResourcename(dto.getResourceName());
                resourceData .setResourceaddress(dto.getResourceAddress());
                resourceData.setContacts(dto.getContacts());
                resourceData.setMobilephone(dto.getMobilePhone());
                resourceData.setCount(dto.getCount());
                resourceData.setIntime(dto.getInTime());
                successRecords.add(resourceData);
            }

            // 批量插入成功记录
            if (!successRecords.isEmpty()) {
                for(ResourceData data :successRecords){
                    log.info("data:{}",data);
                    resourceDataMapper.insert(data);
                }

            }

        } catch ( IOException | ParseException e) {
            errorMessages.add("文件读取失败：" + e.getMessage());
        }
        if(errorMessages.isEmpty()){
            return Collections.singletonMap("success","OK");
        }

        return Collections.singletonMap("errors", errorMessages);
    }

    private ResourceDataImportDTO readRow(Row row) throws ParseException {
        ResourceDataImportDTO dto = new ResourceDataImportDTO();

        dto.setChineseRescateName(getStringValue(row.getCell(0)));
        dto.setResourceName(getStringValue(row.getCell(1)));
        dto.setResourceAddress(getStringValue(row.getCell(2)));
        dto.setContacts(getStringValue(row.getCell(3)));
//        dto.setMobilePhone(getStringValue(row.getCell(4)));
        // 特别处理手机号码，确保它总是作为字符串处理
        Cell mobilePhoneCell = row.getCell(4);
        if (mobilePhoneCell != null && mobilePhoneCell.getCellType() == CellType.NUMERIC) {
            // 如果是数字类型，则使用String.format保留所有数字，避免科学计数法
            dto.setMobilePhone(String.format("%d", (long) mobilePhoneCell.getNumericCellValue()));
        } else {
            dto.setMobilePhone(getStringValue(mobilePhoneCell));
        }
        // 假设 getIntegerValue 和 getDateValue 方法已经存在，这里只展示调用部分的优化

// 空指针检查
        Cell countCell = row.getCell(5);
        Cell inTimeCell = row.getCell(6);

        if (countCell != null && !countCell.toString().trim().isEmpty()) {
            try {
                dto.setCount(BigDecimal.valueOf(getIntegerValue(countCell)));
            } catch (NumberFormatException e) {
                // 处理转换异常，例如记录日志或设置默认值
                System.err.println("Error converting integer value: " + e.getMessage());
                dto.setCount(null); // 设置默认值
            }
        } else {
            // 处理空值，例如记录日志或设置默认值
            System.err.println("Count cell is null");
            dto.setCount(null); // 设置默认值
        }

        if (inTimeCell != null) {
            try {
                dto.setInTime(getDateValue(inTimeCell));
            } catch (ParseException e) {
                // 处理解析异常，例如记录日志或设置默认值
                System.err.println("Error parsing date value: " + e.getMessage());
                dto.setInTime(null); // 设置默认值
            }
        } else {
            // 处理空值，例如记录日志或设置默认值
            System.err.println("InTime cell is null");
            dto.setInTime(null); // 设置默认值
        }

//        dto.setCount(BigDecimal.valueOf(getIntegerValue(row.getCell(5))));
//        dto.setInTime(getDateValue(row.getCell(6)));

        return dto;
    }

    private String getStringValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) return "";
        if (cell.getCellType() == CellType.STRING) return cell.getStringCellValue().trim();
        return cell.toString().trim();
    }

    private Integer getIntegerValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) return null;
        return (int) cell.getNumericCellValue();
    }

//    private Date getDateValue(Cell cell) throws ParseException {
//        if (cell == null || cell.getCellType() == CellType.BLANK) return null;
//        if (cell.getCellType() == CellType.STRING) {
//            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//            return format.parse(cell.getStringCellValue());
//        }
//        return cell.getDateCellValue();
//    }

    private Date getDateValue(Cell cell) throws ParseException {
        if (cell == null || cell.getCellType() == CellType.BLANK) return null;

        if (cell.getCellType() == CellType.STRING) {
            // 尝试解析多种可能的日期格式
            String dateStr = cell.getStringCellValue().trim();
            for (SimpleDateFormat format : Arrays.asList(
                    new SimpleDateFormat("yyyy-MM-dd"),
                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"),
                    new SimpleDateFormat("yyyyMMdd"),
                    new SimpleDateFormat("yyyyMMddHHmmss")
            )) {
                try {
                    return format.parse(dateStr);
                } catch (ParseException ignored) {
                    // 如果解析失败，尝试下一个格式
                }
            }
            throw new ParseException("无法解析日期：" + dateStr, 0);
        }
        return cell.getDateCellValue();
    }

    private String convertChineseRescateToId(String chineseName) {
        log.info("chineseName:{}",chineseName);
        // 实际应用中应从数据库查询
        LambdaQueryWrapper<ResourceCategory> wrapper =new LambdaQueryWrapper<ResourceCategory>();
        wrapper.eq(ResourceCategory::getRescatename,chineseName);
        return resourceCategoryMapper.selectOne(wrapper).getRescateid();
    }




    /**
     * 导出资源数据到Excel文件
     *
     * @param response HTTP响应对象
     */
    public void exportResourcesToExcel(HttpServletResponse response) {
        // 获取所有资源数据
        List<ResourceData> resources = resourceDataMapper.selectList(null);

        // 创建工作簿和表格
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Resource Data");

        // 定义表头和内容样式
        CellStyle headerStyle = createHeaderCellStyle(workbook);
        CellStyle contentStyle = createContentCellStyle(workbook);

        CellStyle dateCellStyle = createDateCellStyle(workbook); // 日期样式
        CellStyle textCellStyle = createTextCellStyle(workbook); // 文本样式

        // 设置列宽
        for (int i = 0; i < 8; i++) {
            sheet.setColumnWidth(i, 20 * 256); // 每列宽度为20个字符
        }

        // 写入表头
        String[] headers = {"资源类别", "资源名称", "资源地址", "联系人", "联系人手机号", "数量", "入库时间"};
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(40); // 行高40点
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }

//        // 写入内容
//        int rowNum = 1;
//        for (ResourceData resource : resources) {
//            Row row = sheet.createRow(rowNum++);
//            row.setHeightInPoints(40); // 行高40点
//            log.info("rescateid:{}",resource.getRescateid());
//            ResourceCategory category = resourceCategoryMapper.selectById(resource.getRescateid());
//            if(!ObjectUtils.isEmpty(category)){
//            row.createCell(0).setCellValue(category.getRescatename());
//            }
//            row.createCell(1).setCellValue(resource.getResourcename());
//            row.createCell(2).setCellValue(resource.getResourceaddress());
//            row.createCell(3).setCellValue(resource.getContacts());
////            row.createCell(4).setCellValue(resource.getMobilephone());
//            row.createCell(4).setCellValue("'" + resource.getMobilephone());
//            row.createCell(5).setCellValue(resource.getCount().toString());
////            row.createCell(6).setCellValue(resource.getIntime());
//            // 入库时间（格式化为日期）
//            Cell cellDate = row.createCell(6);
//            cellDate.setCellValue(resource.getIntime());
//            cellDate.setCellStyle(createDateCellStyle(workbook)); // 使用特定的日期样式
//
//
//            // 设置内容样式
//            for (Cell cell : row) {
//                cell.setCellStyle(contentStyle);
//            }
//        }


        // 写入内容
        int rowNum = 1;
        for (ResourceData resource : resources) {
            Row row = sheet.createRow(rowNum++);
            row.setHeightInPoints(40); // 行高40点

            // 资源类别ID
            Cell cellId = row.createCell(0);
            log.info("rescateid:{}",resource.getRescateid());
           ResourceCategory category = resourceCategoryMapper.selectById(resource.getRescateid());
           if(!ObjectUtils.isEmpty(category)){
            cellId.setCellValue(category.getRescatename());
            }

            cellId.setCellStyle(contentStyle);

            // 资源名称
            Cell cellName = row.createCell(1);
            cellName.setCellValue(resource.getResourcename());
            cellName.setCellStyle(contentStyle);

            // 资源地址
            Cell cellAddress = row.createCell(2);
            cellAddress.setCellValue(resource.getResourceaddress());
            cellAddress.setCellStyle(contentStyle);

            // 联系人
            Cell cellContacts = row.createCell(3);
            cellContacts.setCellValue(resource.getContacts());
            cellContacts.setCellStyle(contentStyle);

            // 联系人手机号（作为文本处理）
            Cell cellPhone = row.createCell(4);
            cellPhone.setCellValue(resource.getMobilephone()); // 不加单引号
            cellPhone.setCellStyle(textCellStyle); // 使用文本样式

            // 数量
            Cell cellCount = row.createCell(5);
            cellCount.setCellValue(resource.getCount().toString());
            cellCount.setCellStyle(contentStyle);

            // 入库时间（格式化为日期）
            Cell cellDate = row.createCell(6);
            cellDate.setCellValue(resource.getIntime());
            cellDate.setCellStyle(dateCellStyle); // 使用日期样式
        }


        // 输出Excel文件
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("resource_data.xlsx", "UTF-8"));
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 创建日期样式
    private CellStyle createDateCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 设置日期格式
        CreationHelper createHelper = workbook.getCreationHelper();
        style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));

        return style;
    }


    // 创建文本样式
    private CellStyle createTextCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("@")); // 设置为文本格式
        return style;
    }

    private CellStyle createHeaderCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 14);
        font.setBold(true);
        style.setFont(font);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

   // 表头字体加粗，表头字体14；内容字体12；每列宽度20，行高40；所有单元格要加边框。
    private CellStyle createContentCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }




}
